using System;
using System.Data;
using System.Text;

using Elev.Core.Data;
using Elev.Core.Utility;

namespace Elev.Core.DAL
{
    internal class GradeInternal : SqliteTable
    {
        /// <summary>
        /// Returns all the grades of a given student for a given period
        /// </summary>
        internal DataTable GetGrades(int StudentID, int PeriodID)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT g.* FROM Grade g ");
            SQL.AppendLine("INNER JOIN Exam e ON e.ID = g.ExamID ");
            SQL.AppendLine("INNER JOIN Period p ON p.ID = e.PeriodID ");
            SQL.AppendLine("WHERE g.StudentID = @StudentID AND p.ID = @PeriodID ");
            SQL.AppendLine("ORDER BY CAST(e.Date AS Date) DESC");

            return SqliteAdapter.Get().Execute(SQL.ToString(), "@StudentID", StudentID, "@PeriodID", PeriodID);
        }

        /// <summary>
        /// Returns the grade of a given student for a given exam
        /// </summary>
        internal DataTable GetGrade(int StudentID, int ExamID)
        {
            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT g.* FROM Grade g ");
            SQL.AppendLine("INNER JOIN Exam e ON e.ID = g.ExamID ");
            SQL.AppendLine("WHERE g.StudentID = @StudentID AND e.ID = @ExamID");

            return SqliteAdapter.Get().Execute(SQL.ToString(), "@StudentID", StudentID, "@ExamID", ExamID);
        }

        internal int Add(int ExamID, int StudentID, double Mark, string Note)
        {
            if (ExamID < 1 || StudentID < 1)
                throw new ArgumentException("An exam ID and a student ID are required.");

            if (Mark < 0)
                throw new ArgumentException("A mark must be a positive number.");

            string SQL = "INSERT INTO Grade(ExamID, StudentID, Mark, Note) " +
                            "VALUES(@ExamID, @StudentID, @Mark, @Note)";

            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ExamID", ExamID, "@StudentID", StudentID,
                                "@Mark", Mark, "@Note", Note);

            return Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar("SELECT MAX(ID) FROM Grade"));
        }

        internal void Update(int ExamID, int StudentID, double Mark, string Note)
        {
            if (Mark < -1)
                throw new ArgumentException("A mark must be a positive number, or -1 if not set.");

            string SQL = "UPDATE Grade Set Mark = @Mark, Note = @Note WHERE ExamID = @ExamID AND StudentID = @StudentID";

            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Mark", Mark, "@Note", Note, "@ExamID", ExamID, "@StudentID", StudentID);
        }

        internal void Delete(int ExamID, int StudentID)
        {
            string SQL = "DELETE FROM Grade WHERE ExamID = @ExamID AND StudentID = @StudentID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ExamID", ExamID, "@StudentID", StudentID);
        }

        internal void Delete(int ID)
        {
            string SQL = "DELETE FROM Grade WHERE ID = @ID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ID", ID);
        }

        #region SqliteTable Members

        public override void Create()
        {
            string SQL = "CREATE TABLE [Grade] (" +
                            " ID INTEGER DEFAULT '' NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE," +
                            " ExamID INTEGER DEFAULT '' NOT NULL," +
                            " StudentID INTEGER DEFAULT '' NOT NULL," +
                            " Mark NUMERIC DEFAULT '' NOT NULL," +
                            " Note TEXT)";

            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        public override void Drop()
        {
            string SQL = "DROP TABLE [Grade]";
            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        #endregion
    }
}
